package classes;

import java.sql.*;
import java.util.Map;
import java.util.Map.Entry;
import java.util.logging.*;
import javax.annotation.Resource;
import javax.naming.InitialContext;
import javax.sql.DataSource;



import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class AdminDB {

    //@Resource(name="jdbc/myDatasource")
    //public DataSource pool;
    public AdminDB() {
    }

    public Connection getConnection() throws SQLException {
        Connection con = null;
        try {
            InitialContext ic;

            ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("conBasePrueba");//JNDI
            con = ds.getConnection();

        } catch (NamingException ex) {
            Logger.getLogger(AdminDB.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
        }
        return con;
    }

    public Integer getID(String tabla, String condicion) throws SQLException {
        ResultSet rset = null;
        Connection conn = this.getConnection();
        int id = 0;
        try {
            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT ID FROM `F1shopOnline`.`" + tabla + "` WHERE " + condicion);
            }
            rset = stmt.executeQuery();
            if (rset.next()) {
                id = rset.getInt("ID");
            }

            if (stmt != null) {
                stmt.close();
            }

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
        } catch (Exception e) {
            Logger.getLogger("Recuperar ID").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
        } finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetById").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetById").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
            return id;
        }
    }

    public void borrarFila(String tabla, String condicion) throws SQLException {
        Connection conn = this.getConnection();
        try {
            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("DELETE FROM `F1shopOnline`.`" + tabla + "` WHERE " + condicion);
            }
            stmt.executeUpdate();
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
        } catch (Exception e) {
            Logger.getLogger("BorrarFila").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
        } finally {

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("BorrarFila").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public boolean guardar(ObjetoDB o) throws SQLException {
        Connection conn = this.getConnection();
        try {
            PreparedStatement stmt = null;
            String sentencia = "INSERT INTO `F1shopOnline`.`" + o.getTabla() + "` (";
            String values = ") VALUES (";

            boolean first = true;
            for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {
                if (first) {
                    sentencia += "`" + entry.getKey() + "`";//concatena
                    values += "'" + entry.getValue() + "'";
                    first = false;
                } else if (o.getCampos().keySet().iterator().hasNext()) {
                    sentencia += ",`" + entry.getKey() + "`";
                    values += ",'" + entry.getValue() + "'";
                }
            }
            sentencia += values + ");";
            if (null == stmt) {
                stmt = conn.prepareStatement(sentencia);
            }
            stmt.executeUpdate();

            System.out.println("Esta es la sentencia con la que guardamos: " + sentencia);
            if (stmt != null) {
                stmt.close();
            }
            return true;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return false;
        } catch (Exception e) {
            Logger.getLogger("Guardado en " + o.getTabla()).log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        } finally {

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("Guardar").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public boolean actualizar(ObjetoDB o) throws SQLException {
        Connection conn = this.getConnection();
        try {
            PreparedStatement stmt = null;

            String sentencia = "UPDATE `F1shopOnline`.`" + o.getTabla() + "` SET ";
            String where = " WHERE ";

            boolean first = true;
            for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {
                if (first) {
                    sentencia += entry.getKey() + "='" + entry.getValue() + "'";
                    where += "ID=" + o.getId();
                    first = false;
                } else if (o.getCampos().keySet().iterator().hasNext()) {
                    sentencia += "," + entry.getKey() + "='" + entry.getValue() + "'";
                }
            }
            sentencia += where;

            if (null == stmt) {
                stmt = conn.prepareStatement(sentencia);
            }
            stmt.executeUpdate();

            System.out.println("Esta es la sentencia con la que actualizamos: " + sentencia);
            if (stmt != null) {
                stmt.close();
            }

            return true;

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return false;
        } catch (Exception e) {
            Logger.getLogger("Actualizado " + o.getTabla()).log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        } finally {

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("Actualizar").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public boolean borrar(ObjetoDB o) throws SQLException {
        Connection conn = this.getConnection();
        try {
            PreparedStatement stmt = null;
            String sentencia = "DELETE FROM `F1shopOnline`.`" + o.getTabla() + "` WHERE ";
            boolean first = true;
            for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {
                if (first) {
                    sentencia += entry.getKey() + "='" + entry.getValue() + "'";
                    first = false;
                } else if (o.getCampos().keySet().iterator().hasNext()) {
                    sentencia += " AND " + entry.getKey() + "='" + entry.getValue() + "'";
                }
            }
            if (null == stmt) {
                stmt = conn.prepareStatement(sentencia);
            }
            stmt.executeUpdate();
            System.out.println("Esta es la sentencia con la que borramos: " + sentencia);
            if (stmt != null) {
                stmt.close();
            }
            return true;

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return false;
        } catch (Exception e) {
            Logger.getLogger("Borrado " + o.getTabla()).log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("Borrar").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public boolean vaciarTabla(ObjetoDB o) throws SQLException {
        Connection conn = this.getConnection();
        try {
            PreparedStatement stmt = null;

            String sentencia = "TRUNCATE `F1shopOnline`.`" + o.getTabla() + "`";

            boolean first = true;
            for (Map.Entry<String, String> entry : o.getCampos().entrySet()) {
                if (first) {
                    sentencia += entry.getKey() + "='" + entry.getValue() + "'";
                    first = false;
                } else if (o.getCampos().keySet().iterator().hasNext()) {
                    sentencia += " AND " + entry.getKey() + "='" + entry.getValue() + "'";
                }
            }
            if (null == stmt) {
                stmt = conn.prepareStatement(sentencia);
            }
            stmt.executeUpdate();

            System.out.println("Esta es la sentencia con la que borramos TODA LA TABLA: " + sentencia);
            if (stmt != null) {
                stmt.close();
            }
            return true;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return false;
        } catch (Exception e) {
            Logger.getLogger("Borrado " + o.getTabla()).log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        } finally {

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("VaciarTabla").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public boolean autentificar(String email, String pass) throws SQLException {
        Connection conn = this.getConnection();
        ResultSet rset = null;
        boolean login = false;
        try {
            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT email, pass FROM `F1shopOnline`.`user` WHERE email='" + email + "' AND pass='" + pass + "';");
            }
            rset = stmt.executeQuery();


            if (rset.next()) {
                login = true;
            }

            if (stmt != null) {
                stmt.close();
            }

            return login;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return false;
        } catch (Exception e) {
            Logger.getLogger("Autentificar").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        } finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("Autentificar").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("Autentificar").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public boolean existeUsuario(Connection conn, String email) throws SQLException {
        ResultSet rset = null;
        boolean login = false;
        try {

            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT email FROM `F1shopOnline`.`user` WHERE email='" + email + "';");
            }
            rset = stmt.executeQuery();
            if (rset.next()) {
                login = true;
            }

            if (stmt != null) {
                stmt.close();
            }


            return login;

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return false;
        } catch (Exception e) {
            Logger.getLogger("Autentificar").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        } finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("ExisteUsuario").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("ExisteUsuario").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public String recoverMail(Connection conn, String email) throws SQLException {
        ResultSet rset = null;
        String pass = null;
        System.out.println("Email pasado para recuperar:" + email);
        if (email == null) {
            return "error";
        }
        try {
            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT pass FROM `F1shopOnline`.`user` WHERE email='" + email + "';");
            }
            rset = stmt.executeQuery();

            if (rset.next()) {

                pass = rset.getString("pass");

                return pass;
            }
            if (stmt != null) {
                stmt.close();
            }

            return "error";
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return "Unknown";
        } catch (Exception e) {
            Logger.getLogger("recover").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return "Unknown";
        } finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("RecoverMail").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("RecoverMail").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public String toSQLDate(String fecha) {
        String dia = fecha.substring(0, 2);
        String mes = fecha.substring(3, 5);
        String ano = fecha.substring(6);

        return ano + "-" + mes + "-" + dia;
    }

    public String toDMY(String fecha) {
        String ano = fecha.substring(0, 4);
        String mes = fecha.substring(5, 7);
        String dia = fecha.substring(8);

        String mmm = null;
        int m = Integer.parseInt(mes);
        switch (m) {
            case 1:
                mmm = "ENE";
            case 2:
                mmm = "FEB";
            case 3:
                mmm = "MAR";
            case 4:
                mmm = "ABR";
            case 5:
                mmm = "MAY";
            case 6:
                mmm = "JUN";
            case 7:
                mmm = "JUL";
            case 8:
                mmm = "AGO";
            case 9:
                mmm = "SEP";
            case 10:
                mmm = "OCT";
            case 11:
                mmm = "NOV";
            case 12:
                mmm = "DIC";

                break;
            default:
                throw new AssertionError();
        }

        return dia + " " + mmm + " " + ano;
    }

    public String toFyH(String fecha, String hora) {
        String dia = fecha.substring(0, 2);
        String mmm = fecha.substring(3, 6);
        String ano = fecha.substring(7);

        String mes = null;
        if (mmm.equals("ENE")) {
            mes = "1";
        }
        if (mmm.equals("FEB")) {
            mes = "2";
        }
        if (mmm.equals("MAR")) {
            mes = "3";
        }
        if (mmm.equals("ABR")) {
            mes = "4";
        }
        if (mmm.equals("MAY")) {
            mes = "5";
        }
        if (mmm.equals("JUN")) {
            mes = "6";
        }
        if (mmm.equals("JUL")) {
            mes = "7";
        }
        if (mmm.equals("AGO")) {
            mes = "8";
        }
        if (mmm.equals("SEP")) {
            mes = "9";
        }
        if (mmm.equals("OCT")) {
            mes = "10";
        }
        if (mmm.equals("NOV")) {
            mes = "11";
        }
        if (mmm.equals("DIC")) {
            mes = "12";
        }

        String out = dia + "-" + mes + "-" + ano + " a las " + hora;
        return out;
    }

    public String toREF(String fecha) {
        String dia = fecha.substring(0, 2);
        String mmm = fecha.substring(3, 6);
        String ano = fecha.substring(9);

        String mes = null;
        if (mmm.equals("ENE")) {
            mes = "1";
        }
        if (mmm.equals("FEB")) {
            mes = "2";
        }
        if (mmm.equals("MAR")) {
            mes = "3";
        }
        if (mmm.equals("ABR")) {
            mes = "4";
        }
        if (mmm.equals("MAY")) {
            mes = "5";
        }
        if (mmm.equals("JUN")) {
            mes = "6";
        }
        if (mmm.equals("JUL")) {
            mes = "7";
        }
        if (mmm.equals("AGO")) {
            mes = "8";
        }
        if (mmm.equals("SEP")) {
            mes = "9";
        }
        if (mmm.equals("OCT")) {
            mes = "10";
        }
        if (mmm.equals("NOV")) {
            mes = "11";
        }
        if (mmm.equals("DIC")) {
            mes = "12";
        }

        String out = dia + mes + ano;
        return out;
    }
}
